package com.etooling.pdm.repository;

import com.etooling.framework.repository.BaseRepository;
import com.etooling.pdm.entity.GoodsSpecsPropSetting;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public interface GoodsSpecsPropSettingRepository extends BaseRepository<GoodsSpecsPropSetting> {


    /**
     * 获取，最大序号，使用产品id
     *
     * @param goodsId
     * @return
     */
    @Query(nativeQuery = true, value = "select COALESCE(max(seq), 0) from pdm.t_goods_specs_prop_setting where goods_id=?1 and status !='" + GoodsSpecsPropSetting.STATUS_DELETE + "' ")
    Integer getMaxSeq(String goodsId);


    /**
     * 获取。name，id
     *
     * @param goodsId
     * @return
     */
    @Query(nativeQuery = true, value = "select name, id from pdm.t_goods_specs_prop_setting where goods_id = ?1 order by seq")
    List<Map<String, Object>> getIdAndName(String goodsId);


    /**
     * 统计一个序号的数量，使用产品id和序号
     *
     * @param goodsId
     * @param seq
     * @return
     */
    @Query(nativeQuery = true, value = "select count(*) from pdm.t_goods_specs_prop_setting where goods_id=?1 and seq=?2 and status !='" + GoodsSpecsPropSetting.STATUS_DELETE + "'")
    Integer countSeq(String goodsId, Integer seq);


    /**
     * 统计。一个序号的数量，使用id，产品id，序号
     *
     * @param id
     * @param goodsId
     * @param seq
     * @return
     */
    @Query(nativeQuery = true, value = "select count(*) from pdm.t_goods_specs_prop_setting where id != ?1 and goods_id=?2 and seq=?3 and status !='" + GoodsSpecsPropSetting.STATUS_DELETE + "'")
    Integer countSeq(String id, String goodsId, Integer seq);


    @Query(nativeQuery = true, value = "select tgsps.id, tgsps.name, tgspv.code, tgspv.value from pdm.t_goods_specs_prop_setting tgsps left join pdm.t_goods_specs_prop_values tgspv on tgsps.id = tgspv.setting_id and tgspv.goods_specs_id = ?2 where tgsps.goods_id = ?1 order by tgsps.seq ")
    List<Map<String, Object>> getPropList(String goodsId, String goodsSpecsId);


    @Query(nativeQuery = true,value = "select id, company_id, name, seq, status, goods_id from pdm.t_goods_specs_prop_setting where goods_id = ?1 and status = ?2 ")
    List<Map<String,Object>> getAllData(String goodsId, String status);


}
